package excelPa;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 该类完成解析"comment.xlsx"和
 * “01新-省回流数据第一二两批情况100张表（王彬）.xlsx”的解析
 * 完成合并单元格的解析，根据输入的英文表名生成修改注释的sql脚本
 */
public class ExcelProcess {

    public static void main(String[] args) throws IOException {
        Map<String,String> nameMap = new HashMap<>();
        Map<String,String> sqlMap = new HashMap<>();
        //表名
        String tablename="v_yz_ods_zj_fgw_dsc_da019_zdsswfajdsrmd_valid_old_df";

        readExcelToMap("src/main/resources/comment.xlsx",nameMap);
        readPCcomeBackExcel("src/main/resources/01新-省回流数据第一二两批情况100张表（王彬）.xlsx",
                tablename.trim(),nameMap,sqlMap);
        //System.out.println(nameMap.get(tablename));
        createSQLfile(tablename,sqlMap);

    }


    public static void createSQLfile(String tablename,Map sqlMap) throws IOException {
        FileWriter writer=null;
        FileOutputStream out = null;
        PrintWriter printWriter = null;
        File file = new File("src/main/sqlFile/"+tablename);
        StringBuilder sb= new StringBuilder();
        for (Object key : sqlMap.keySet()) {
            String eName=(String)key;
            String cName = (String)sqlMap.get(eName);
            String sql="alter table "+ tablename +
                    " CHANGE COLUMN "+eName+" "+ eName+ " string COMMENT "+"'"+cName+"'"+";";
            System.out.println(sql);
            sb.append(sql+"\n");
        }
        if(file.exists()){
            file.delete();
            file.createNewFile();
            out = new FileOutputStream(file);
            printWriter = new PrintWriter(out);
            printWriter.write(sb.toString());
            printWriter.flush();
        }else {
            file.createNewFile();
            out = new FileOutputStream(file);
            printWriter = new PrintWriter(out);
            printWriter.write(sb.toString());
            printWriter.flush();
        }
        out.close();
        printWriter.close();


    }





    /**
     * 读取全部中英文名形成map
     * @param excelName
     * @throws IOException
     */
    public static void readExcelToMap(String excelName,Map map) throws IOException {
        final FileInputStream in = new FileInputStream(excelName);
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet3 = wb.getSheet("sheet3");
        final int totalRows = sheet3.getPhysicalNumberOfRows();
        //System.out.println(totalRows);
        XSSFRow row;
        for (int i=1;i<=totalRows-1;i++){
            row = sheet3.getRow(i);
            XSSFCell englishNameCell = row.getCell(1);
            XSSFCell chineseNameCell = row.getCell(0);
            String englishName = englishNameCell.toString().trim();
            String chineseName = chineseNameCell.toString().trim();
            map.put(englishName, chineseName);
        }

        in.close();


    }

    public static void readPCcomeBackExcel(String filename,String englishname,Map map,Map sqlMap) throws IOException {
        String chinesename = (String) map.get(englishname);
        final FileInputStream in = new FileInputStream(filename);
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet1 = wb.getSheet("sheet1");
        final int totalRows = sheet1.getPhysicalNumberOfRows();
        final List<CellRangeAddress> conbineCell = getConbineCell(sheet1);
        int count=0;
        //System.out.println(conbineCell.size());
        for (CellRangeAddress cellAddresses : conbineCell) {
            int firstRow = cellAddresses.getFirstRow();
            int lastRow = cellAddresses.getLastRow();
            final XSSFCell cell = sheet1.getRow(firstRow).getCell(1);
            final String cellValue = getCellValue(cell).trim();
            if(cellValue.contains(chinesename)){
                int fieldFirst=firstRow;
                int fieldLast=lastRow;
                for(int i=fieldFirst;i<=fieldLast;i++) {
                    //拿到对应表的字段和注释
                    String chinese = sheet1.getRow(i).getCell(2).getStringCellValue();
                    String english = sheet1.getRow(i).getCell(4).getStringCellValue();
                    sqlMap.put(english,chinese);
                }
                break;
            }
        }




    }


    /**
     * 获取合并单元格保存在list中
     * @param sheet
     * @return
     */
    public static List<CellRangeAddress>  getConbineCell(XSSFSheet sheet){
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获取该sheet中合并单元格的数量
        int numMergedRegions = sheet.getNumMergedRegions();
        System.out.println("合并单元格的数量:"+numMergedRegions);
        for(int i=0;i<numMergedRegions;i++){
            final CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            list.add(mergedRegion);
        }
        return list;
    }




    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
       if(cell == null) return "";
//        if(cell.getCellType() == Cell.CELL_TYPE_STRING){
//            return cell.getStringCellValue();
//        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
//            return String.valueOf(cell.getBooleanCellValue());
//        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
//            return cell.getCellFormula() ;
//        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
//            return String.valueOf(cell.getNumericCellValue());
//        }
        return cell.getStringCellValue();
    }

}
